package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import connectionMSqL.MySQLConn;
import entity.Aluno;
import entity.Materia;
import entity.Professor;
import entity.Turma;

public class TurmaDAO {

	public static boolean insert(Turma turma, Professor professor, Aluno aluno,
			Materia materia) {
		boolean salvou = false;

		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			int paramIndex = 1;
			ps = conn
					.prepareStatement("INSERT INTO Turma (nome,idMateria,matriculaAluno,idProfessor)"
							+ "VALUES (?,?,?,?)");
			ps.setString(paramIndex++, turma.getNome());
			ps.setObject(paramIndex++, materia.getId());
			ps.setObject(paramIndex++, aluno.getMatricula());
			ps.setObject(paramIndex++, professor.getId());

			ps.executeUpdate();
			salvou = true;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return salvou;
	}

//	public static Turma createTurma(ResultSet rs) throws SQLException {
	//	Turma turma = new Turma();

		//turma.setId(rs.getInt("id"));
		//turma.setNome(rs.getString("nome"));
		//turma.setIdMateria((Materia) rs.getObject("idMateria"));
		//turma.setIdProfessor((Professor) rs.getObject("idProfessor"));
		//turma.setMatriculaAluno((Aluno) rs.getObject("matriculaAluno"));
	//	return turma;
//	}

	public static Turma createTurmaList(ResultSet rs)
			throws SQLException {
		Turma turma = new Turma();

		turma.setId(new Integer (rs.getString("idTurma")));
		turma.setNome(rs.getString("nomeTurma"));
		turma.setNomeMateria(rs.getString("nomeMateria"));
		turma.setNomeProfessor(rs.getString("nomeProfessor"));
		turma.setQtdeAluno(new Integer (rs.getString("qtdeAlunos")));
		
		return turma;
	}

	public static List<Turma> getAll() {
		List<Turma> turmas = new ArrayList<Turma>();
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn
					.prepareStatement(""
							+ "SELECT t.id AS idTurma, t.nome AS nomeTurma, m.nome AS nomeMateria,"
							+ " p.nome AS nomeProfessor, COUNT(matriculaAluno) AS qtdeAlunos"
							+ " FROM turma t"
							+ " join materia m on t.idMateria = m.id"
							+ " join professor p on t.idProfessor = p.id" 
							+ " GROUP BY t.nome"
							+ " ORDER BY t.id");
			rs = ps.executeQuery();
			while (rs.next()) {
				
				Turma turma = createTurmaList(rs);
				turmas.add(turma);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();

			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}

		return turmas;
	}


	

	public static List<Turma> findById(int id) {
			List<Turma> turmas = new ArrayList<Turma>();
			Connection conn = MySQLConn.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn
						.prepareStatement(""
								+ "SELECT t.id AS idTurma, t.nome AS nomeTurma, m.nome AS nomeMateria,"
								+ " p.nome AS nomeProfessor, COUNT(matriculaAluno) AS qtdeAlunos"
								+ " FROM turma t"
								+ " join materia m on t.idMateria = m.id"
								+ " join professor p on t.idProfessor = p.id"
								+ " group by t.id");
				rs = ps.executeQuery();
				while (rs.next()) {
					
					Turma turma = createTurmaList(rs);
					turmas.add(turma);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			} finally {
				try {
					if (rs != null)
						rs.close();
					if (ps != null)
						ps.close();
					if (conn != null)
						conn.close();

				} catch (SQLException e) {
					System.out.println(e.getMessage());
				}
			}

			return turmas;
		
	}
	public static boolean update(Turma turma) {
		boolean salvou = false;
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			String query = ("UPDATE turma" + " SET nome='" + turma.getNome()
					+ "'" + ", idMateria=" + turma.getIdMateria()
					+ ", matriculaAluno = " + turma.getMatriculaAluno()
					+ ", idProfessor=" + turma.getIdProfessor()
					+ " WHERE id = " + turma.getId());
			ps = conn.prepareStatement(query);
			ps.executeUpdate();
			salvou = true;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return salvou;
	}
	
	public static boolean delete(String nome) {

		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			
			ps = conn.prepareStatement("DELETE FROM turma WHERE nome='" + nome+"'");
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();

			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return false;
			}
		}

		return true;
	}
}